/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package phd.changeidentifiers.testing.display.database;

import java.io.BufferedReader;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.naming.Reference;
import javax.naming.StringRefAddr;
import javax.sql.DataSource;

/**
 *
 * @author mpd209
 */
public class DatabaseConnection {

    private static DataSource dataSource;
    private static String username;
    private static String password;
    private static String host;
    private static String database;

    public static Connection getConnection() throws SQLException, DataSourceNotInitialisedException {
        if (!hasDataSource()) {
            throw new DataSourceNotInitialisedException("Cannot get connection, " +
                    "datasource has not yet been created.");
        }
        return dataSource.getConnection();
    }

    public static void closeConnection(Connection conn) {
        try {
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
        } catch (SQLException ex) {
            //ignore
        }
    }

    public static void closeStatement(Statement statement) {
        try {
            if (statement != null) {
                statement.close();
            }
        } catch (SQLException ex) {
            //ignore
        }
    }

    public static void closeResultSet(ResultSet resultSet) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
        } catch (SQLException ex) {
            //ignore
        }
    }

    public static int getLastInsertIdInt(Connection conn) throws SQLException {
        try {
            String id = getLastInsertId(conn);
            return (id != null ? Integer.parseInt(id) : -1);
        } catch (NumberFormatException numberFormatException) {
            return -1;
        }
    }

    public static String getLastInsertId(Connection conn) throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;
        try {
            statement = conn.prepareStatement("SELECT LAST_INSERT_ID() AS 'id'");
            result = statement.executeQuery();
            String id = null;
            if (result.next()) {
                id = result.getString("id");
            }
            return id;
        } finally {
            DatabaseConnection.closeStatement(statement);
            DatabaseConnection.closeResultSet(result);
        }
    }

    public static boolean tableExists(String tablename) throws SQLException, DataSourceNotInitialisedException {
        Connection conn = null;
        try {
            conn = DatabaseConnection.getConnection();
            return tableExists(conn, tablename);
        } finally {
            DatabaseConnection.closeConnection(conn);
        }
    }

    public static boolean tableExists(Connection conn, String tablename) throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;
        try {
            statement = conn.prepareStatement("SHOW TABLES LIKE ?");
            statement.setString(1, tablename);
            result = statement.executeQuery();
            return (result.next());
        } finally {
            DatabaseConnection.closeStatement(statement);
            DatabaseConnection.closeResultSet(result);
        }
    }

    public static boolean hasDataSource() {
        return (dataSource != null);
    }
    public static class ScriptResult{
        private String output;
        private String error;
        private int exitValue;
        public ScriptResult(String output, String error, int exitValue){
            this.output = output;
            this.error =error;
            this.exitValue = exitValue;
        }

        /**
         * @return the output
         */
        public String getOutput() {
            return output;
        }

        /**
         * @return the error
         */
        public String getError() {
            return error;
        }

        /**
         * @return the exitValue
         */
        public int getExitValue() {
            return exitValue;
        }

    }
    private static class ScriptStreamReader implements Runnable{

        private InputStream stream;
        private String result;
        private boolean finished;
        public ScriptStreamReader(InputStream stream){
            this.stream = stream;
            this.finished = false;
        }

        public void run() {
            try{
                StringBuffer output = new StringBuffer();
                try {
                    BufferedReader reader = new BufferedReader(new InputStreamReader(stream));
                    String line = null;
                    while ((line = reader.readLine()) != null) {
                        output.append(line+"\n");
                    }
                } catch (IOException iOException) {
                    output.append("IOException: '"+iOException.getMessage()+"'");
                }finally{
                    try {
                        if(stream!=null) stream.close();
                    } catch (IOException ex) {
                        //Nothing we can do
                    }
                }
                result = output.toString();
            }finally{
                this.finished = true;
            }
        }

        /**
         * @return the result
         */
        public String getResult() {
            return result;
        }

        /**
         * @return the finished
         */
        public boolean isFinished() {
            return finished;
        }
        
    }
    public static ScriptResult runScript(String scriptLocation) throws IOException,InterruptedException {
        OutputStream out = null;
        try{
            if(!scriptLocation.matches("^.{2,}\\.sql$")){
                return new ScriptResult("", "Script Location is invalid: '"+scriptLocation+"'", 1);
            }
            String osName = System.getProperty("os.name" ).toLowerCase();
            if(osName.contains("windows")){
                if(scriptLocation.charAt(0)=='/'){
                    scriptLocation = scriptLocation.substring(1);
                }
                scriptLocation = scriptLocation.replace("/", "\\\\");
            }
            String mysql = "mysql -h " + DatabaseConnection.host + " " +
                        "-D " + DatabaseConnection.database + " " +
                        "-u " + DatabaseConnection.username + " " +
                        "--password=" + DatabaseConnection.password + " " +
                        " < \"" + scriptLocation + "\"";
//            System.out.println("OSName: "+osName);
            String[] cmd = new String[3];
            if(osName.equals("linux")){
                cmd = new String[]{
                    "sh",
                    "-c",
                    mysql
                };
            }else if(osName.equals("windows 95")){

                cmd = new String[]{
                    "command.com",
                    "/C",
                    mysql
                };
            }else if(osName.contains("windows")){
                cmd = new String[]{
                    "cmd.exe",
                    "/C",
                    mysql
                };
            }else{
                return new ScriptResult("", "OS: "+osName+" not recognised.", 1);
            }
//            System.out.println("Running cmd "+cmd[0]+" "+cmd[1]+" "+cmd[2]);
            Process process = Runtime.getRuntime().exec(cmd);
//            out = process.getOutputStream();
//            BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(out));
//            writer.write(DatabaseConnection.password);
            ScriptStreamReader inputReader = new ScriptStreamReader(process.getInputStream());
            ScriptStreamReader errorReader = new ScriptStreamReader(process.getErrorStream());
            new Thread(inputReader).start();
            new Thread(errorReader).start();
            int exitVal = process.waitFor();
            while(!inputReader.isFinished() || !errorReader.isFinished()){
                try{
                    Thread.sleep(1);
                }catch(InterruptedException ex){
                    //Ignore
                }
            }
            return new ScriptResult(inputReader.getResult(), errorReader.getResult(), exitVal);
        }finally{
            try{
                if(out!=null)out.close();
            }catch(IOException ex){
                //Nothing we can do
            }
        }
    }

    public static void initialise(String host, String database, String username, String password) throws SQLException {
        try {
            DatabaseConnection.host = host;
            DatabaseConnection.database = database;
            DatabaseConnection.username = username;
            DatabaseConnection.password = password;
//            Hashtable env = new Hashtable();
//            env.put(Context.INITIAL_CONTEXT_FACTORY,
//                    "com.sun.jndi.fscontext.RefFSContextFactory");
//            env.put(Context.PROVIDER_URL, "file:///secamfs/userspace/phd/mpd209/");
            System.setProperty(Context.INITIAL_CONTEXT_FACTORY,
                    "com.sun.jndi.fscontext.RefFSContextFactory");
            if(new File("/olib1/ecs_ug/cspg/mpd209/").isDirectory()){
                System.setProperty(Context.PROVIDER_URL,
                        "file:///secamfs/userspace/phd/mpd209/");
            }else{
                System.setProperty(Context.PROVIDER_URL,
                        "file:///C:\\Users\\Max\\");

            }
            InitialContext ic = new InitialContext();

            // Construct BasicDataSource reference
            Reference ref = new Reference("javax.sql.DataSource",
                    "org.apache.commons.dbcp.BasicDataSourceFactory", null);
            ref.add(new StringRefAddr("driverClassName", "com.mysql.jdbc.Driver"));
            ref.add(new StringRefAddr("url", "jdbc:mysql://"+host+"/"+database));
            ref.add(new StringRefAddr("username", username));
            ref.add(new StringRefAddr("password", password));
            ic.rebind("jdbc_changeIdentifier", ref);

            // Use
            InitialContext ic2 = new InitialContext();
//            Reference reference = (Reference) ic2.lookup("jdbc_changeIdentifier");
//            System.out.println(reference.getClassName());
            dataSource = (DataSource) ic2.lookup("jdbc_changeIdentifier");
            
//            dataSource = (DataSource) ic2.lookup("jdbc_changeIdentifier");
            Connection conn = dataSource.getConnection();

            conn.close();
        } catch (NamingException namingException) {
            throw new SQLException("Naming Exception thrown", namingException);
        }


    }
}
